
https://jingyan.baidu.com/article/647f01158ee0da7f2148a80b.html
https://www.cnblogs.com/linjiqin/archive/2013/06/21/3148066.html



create table treeNodes
(
 id int primary key,
 nodename varchar(20),
 pid int
);
---------------------------------------------------------------------------------------------------

insert into treeNodes values(1,'A',   0);
insert into treeNodes values(2,'B',   1);
insert into treeNodes values(3,'C',   1);
insert into treeNodes values(4,'D',   2);
insert into treeNodes values(5,'E',   2);
insert into treeNodes values(6,'F',   3);
insert into treeNodes values(7,'G',   6);
insert into treeNodes values(8,'H',   0);
insert into treeNodes values(9,'I',   8);
insert into treeNodes values(10,'J',   8);
insert into treeNodes values(11,'K',   8);
insert into treeNodes values(12,'L',   9);
insert into treeNodes values(13,'M',   9);
insert into treeNodes values(14,'N',  12);
insert into treeNodes values(15,'O',  12);
insert into treeNodes values(16,'P',  15);
insert into treeNodes values(17,'Q',  15)
---------------------------------------------------------------------------------------------------

delimiter //

CREATE FUNCTION `getChildLst`(rootId INT)
RETURNS varchar(1000)
BEGIN
  DECLARE sTemp VARCHAR(1000);
  DECLARE sTempChd VARCHAR(1000);

  SET sTemp = '$';
  SET sTempChd =cast(rootId as CHAR);

  WHILE sTempChd is not null DO
    SET sTemp = concat(sTemp,',',sTempChd);
    SELECT group_concat(id) INTO sTempChd FROM treeNodes where FIND_IN_SET(pid,sTempChd)>0;
  END WHILE;
  RETURN sTemp;
END
//
---------------------------------------------------------------------------------------------------

